﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using SmoLiteApi.Helper;
using SmoLiteApi.Sql;
using System.Text.RegularExpressions;

namespace SmoLiteApi
{
    /// <summary/>
    public class Database
    {
        private Server _server;
        private string _name;
        private RecoveryModel _recoveryModel;
        private SchemaCollection _schemaCollection;
        private UserCollection _userCollection;
        private TableCollection _tableCollection;
        private DatabaseRoleCollection _databaseRoleCollection;
        private StoredProcedureCollection _storedProcedureCollection;
        private ViewCollection _viewCollection;
        private DatabaseExtendedPropertyCollection _databaseExtendedPropertyCollection;
        private FullTextCatalogCollection _fullTextCatalogCollection;

        /// <summary/>
        public Database(
            Server server,
            string name)
        {
            this._server = server;
            this._name = name;
            this._schemaCollection = new SchemaCollection(this);
            this._userCollection = new UserCollection(this);
            this._tableCollection = new TableCollection(this);
            this._databaseRoleCollection = new DatabaseRoleCollection(this);
            this._storedProcedureCollection = new StoredProcedureCollection(this);
            this._viewCollection = new ViewCollection(this);
            this._databaseExtendedPropertyCollection = new DatabaseExtendedPropertyCollection(this);
            this._fullTextCatalogCollection = new FullTextCatalogCollection(this);
        }

        /// <summary/>
        public string Name
        {
            get { return this._name; }
            set { this._name = value; }
        }

        /// <summary/>
        public RecoveryModel RecoveryModel
        {
            get { return this._recoveryModel; }
            set { this._recoveryModel = value; }
        }

        /// <summary/>
        public SchemaCollection Schemas
        {
            get { return this._schemaCollection; }
        }

        /// <summary/>
        public UserCollection Users
        {
            get { return this._userCollection; }
        }

        /// <summary/>
        public TableCollection Tables
        {
            get { return this._tableCollection; }
        }

        /// <summary/>
        public DatabaseRoleCollection Roles
        {
            get { return this._databaseRoleCollection; }
        }

        /// <summary/>
        public StoredProcedureCollection StoredProcedures
        {
            get { return this._storedProcedureCollection; }
        }

        /// <summary/>
        public ViewCollection Views
        {
            get { return this._viewCollection; }
        }

        /// <summary/>
        public DatabaseExtendedPropertyCollection ExtendedProperties
        {
            get { return this._databaseExtendedPropertyCollection; }
        }

        /// <summary/>
        public FullTextCatalogCollection FullTextCatalogs
        {
            get { return this._fullTextCatalogCollection; }
        }

        /// <summary/>
        internal SqlConnection SqlConnection
        {
            get { return this._server.FetchSqlConnection(this._name); }
        }

        /// <summary/>
        public void Create()
        {
            DatabaseCreateSql databaseCreateSql = new DatabaseCreateSql();

            // set attributes
            databaseCreateSql.AddObject(new { Name = this._name });

            using (SqlCommandHelper sqlCommandHelper = SqlCommandHelper.FetchSqlCommand(this._server.FetchSqlConnection(), databaseCreateSql.Render()))
            {
                // run the command
                sqlCommandHelper.ExecuteNonQuery();
            }

            DatabaseAlterRecoverySql databaseAlterRecoverySql = new DatabaseAlterRecoverySql();

            // set attributes
            databaseAlterRecoverySql.AddObject(new { Name = this._name });
            databaseAlterRecoverySql.AddObject(new { Full = this._recoveryModel == RecoveryModel.Full });
            databaseAlterRecoverySql.AddObject(new { BulkLogged = this._recoveryModel == RecoveryModel.Bulk });
            databaseAlterRecoverySql.AddObject(new { Simple = this._recoveryModel == RecoveryModel.Simple });

            using (SqlCommandHelper sqlCommandHelper = SqlCommandHelper.FetchSqlCommand(this._server.FetchSqlConnection(), databaseAlterRecoverySql.Render()))
            {
                // run the command
                sqlCommandHelper.ExecuteNonQuery();
            }
        }

        /// <summary/>
        public void SetOwner(string owner)
        {
            using (SqlCommandHelper sqlCommandHelper = SqlCommandHelper.FetchProcedureCommand(this._server.FetchSqlConnection(this._name), Constant.PROCEDURE_CHANGE_DB_OWNER))
            {
                // set inputs
                sqlCommandHelper.Parameters.Add(new SqlParameter(Constant.PARAMETER_LOGINAME, owner));

                // run the command
                sqlCommandHelper.ExecuteNonQuery();
            }
        }

        /// <summary/>
        public void ExecuteNonQuery(string sql)
        {
            Regex regex = new Regex(Constant.REGEX_GO, RegexOptions.IgnoreCase | RegexOptions.Multiline);

            // split the string
            string[] sqlSections = regex.Split(sql);

            foreach (string sqlSection in sqlSections)
            {
                // ignore blankc sections
                if (string.IsNullOrEmpty(sqlSection))
                {
                    continue;
                }

                // execute the sql
                using (SqlCommandHelper sqlCommandHelper = SqlCommandHelper.FetchSqlCommand(this._server.FetchSqlConnection(this._name), sqlSection))
                {
                    // increase the timeout
                    sqlCommandHelper.CommandTimeout = Constant.FIVE_MINUTES;

                    // run the command
                    sqlCommandHelper.ExecuteNonQuery();
                }
            }
        }
    }
}
